Constraint means limitation. As the name suggests it used to limit something. In SQL server constraints are used to limit the type of data that need to insert or update in the table.
In technical term Constraints can be rule that could be table level or column level.
Following are the some of the Constraints that are commonly used in SQL server :
1: Not Null: It Ensure that a column can't be empty.
Example: CREATE TABLE EE_EMPL_CAT (PRSN_INTN_ID int NOT NULL, EMPL_STAT_CD char (20) NOT NULL, NAME char (10), REFERENCE_ID int, );
2: Default: It provide the default value, if there is nothing specified.
Example: ( PRSN_INTN_ID int NOT NULL , EMPL_STAT_CD char (20) NOT NULL , NAME char(10) DEFAULT XXXXX, REFERENCE_ID int, );
3: Unique: It ensures that all the values in the column are unique.
Example: CREATE TABLE EE_EMPL_CAT (PRSN_INTN_ID int NOT NULL UNIQUE , EMPL_STAT_CD char (20) NOT NULL , NAME char (10), REFERENCE_ID int, );
4: Primary Key: It is used to uniquely identified row in the database table.
Example: CREATE TABLE EE_EMPL_CAT (PRSN_INTN_ID int NOT NULL PRIMARY KEY , EMPL_STAT_CD char (20) NOT NULL, NAME char (10), REFERENCE_ID int, );
5: FOREIGN KEY: It is used to uniquely identified row in another database table.
Example: CREATE TABLE CHILD_PRSN CHILD_ID INT NOT NULL, CHILD_NAME CHAR (20), PRSN_INTN_ID INTFOREIGN KEY REFERENCES EE_EMPL_CAT (PRSN_INTN_ID), )
6: Check: It ensure that all values in columns satisfy certain condition
Example: CREATE TABLE EE_EMPL_CAT ( PRSN_INTN_ID int NOT NULL CHECK PRSN_INTN_ID >0, EMPL_STAT_CD char(20) NOT NULL , NAME char(10), REFERENCE_ID int, );
7: Index: It is used to create or retrieve data from the database very quickly.
CREATE INDEX PIndex ON EE_EMPL_CAT(PRSN_INTN_ID, NAME)
Constraint is specified while creating a table or we can use the keyword ALTER
TABLE statement to create constraint even after the table is created.
8: Dropping Constraints:
We can drop any constraint whatever we have define by using the ALTER TABEL with DROP CONSTRAINT.
E.g. ALTER TABLE EE_EMPL_CAT DROP CONSTRAINT PRSN_INTN_ID;
We can use the shortcut for dropping the constraint by using keyword like PRIMARY key,
E.g. ALTER TABLE EE_EMPL_CAT DROP PRIMARY KEY;
Note: PRIMARY KEY and UNIQUE can't be define for single column.
Liked By
Write Answer
What are the different types of Constraints in SQL?
Join MindStick Community
You have need login or register for voting of answers or question.
Abhishek Srivasatava
27-Sep-2016Constraint:
Constraint means limitation. As the name suggests it used to limit something. In SQL server constraints are used to limit the type of data that need to insert or update in the table.
In technical term Constraints can be rule that could be table level or column level.
Following are the some of the Constraints that are commonly used in SQL server :
1: Not Null: It Ensure that a column can't be empty.
2: Default: It provide the default value, if there is nothing specified.
3: Unique: It ensures that all the values in the column are unique.
4: Primary Key: It is used to uniquely identified row in the database table.
5: FOREIGN KEY: It is used to uniquely identified row in another database table.
6: Check: It ensure that all values in columns satisfy certain condition
7: Index: It is used to create or retrieve data from the database very quickly.
TABLE statement to create constraint even after the table is created.
8: Dropping Constraints:
We can drop any constraint whatever we have define by using the ALTER TABEL with DROP CONSTRAINT.
E.g. ALTER TABLE EE_EMPL_CAT DROP CONSTRAINT PRSN_INTN_ID;
We can use the shortcut for dropping the constraint by using keyword like PRIMARY key,
E.g. ALTER TABLE EE_EMPL_CAT DROP PRIMARY KEY;
Note: PRIMARY KEY and UNIQUE can't be define for single column.